var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('./database.sqlite');

// 创建数据表
db.serialize(() => {
  // 景点表
  db.run(`CREATE TABLE IF NOT EXISTS attractions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    location TEXT,
    open_time TEXT,
    ticket_price REAL,
    stock INTEGER DEFAULT 0
  )`);

  // 订单表
  db.run(`CREATE TABLE IF NOT EXISTS orders (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    attraction_id INTEGER NOT NULL,
    user_name TEXT NOT NULL,
    user_phone TEXT NOT NULL,
    quantity INTEGER NOT NULL,
    total_price REAL NOT NULL,
    order_date TEXT DEFAULT (datetime('now')),
    status INTEGER DEFAULT 1, -- 1:有效 0:取消
    FOREIGN KEY(attraction_id) REFERENCES attractions(id)
  )`);

  // 初始化示例景点数据
  db.run(`INSERT INTO attractions (name, description, location, open_time, ticket_price, stock)
          VALUES 
          ('故宫博物院', '明清两代皇家宫殿', '北京东城区', '08:30-17:00', 60, 5000),
          ('长城', '世界文化遗产', '北京延庆区', '06:30-19:00', 40, 8000)`);
});

module.exports = db;